package org.quipu.api.impl;

import java.util.Calendar;
import java.util.Collection;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.ejb.ConcurrencyManagement;
import javax.ejb.ConcurrencyManagementType;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.ejb.TransactionManagement;
import javax.ejb.TransactionManagementType;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import org.apache.log4j.Logger;
import org.joda.time.DateTime;
import org.joda.time.Days;
import org.joda.time.Period;
import org.quipu.AppException;
import org.quipu.NoSuchAccountException;
import org.quipu.NoSuchBookException;
import org.quipu.NoSuchCurrencyException;
import org.quipu.NoSuchObjectException;
import org.quipu.NoSuchTagException;
import org.quipu.NoSuchTransactionException;
import org.quipu.NoSuchUserException;
import org.quipu.api.impl.BookHelperImpl.AccountsSortBy;
import org.quipu.TransactionsFilter;
import org.quipu.TransactionsFilter.And;
//import org.quipu.api.TransactionsFilter.BookIs;
import org.quipu.TransactionsFilter.Condition;
import org.quipu.TransactionsFilter.CurrencyIs;
import org.quipu.TransactionsFilter.DateEqualsBefore;
import org.quipu.TransactionsFilter.DstIs;
import org.quipu.TransactionsFilter.AccountsAre;
import org.quipu.TransactionsFilter.IsApproved;
import org.quipu.TransactionsFilter.IsIncome;
import org.quipu.TransactionsFilter.IsIncomeLoan;
import org.quipu.TransactionsFilter.IsInitialBallance;
import org.quipu.TransactionsFilter.IsNotInitialBallance;
import org.quipu.TransactionsFilter.IsOutcome;
import org.quipu.TransactionsFilter.IsOutcomeLoan;
import org.quipu.TransactionsFilter.Or;
import org.quipu.TransactionsFilter.Order;
import org.quipu.TransactionsFilter.PaymentCurrencyIs;
import org.quipu.TransactionsFilter.SrcIs;
import org.quipu.TransactionsFilter.TagsAre;
import org.quipu.api.BookHelper;
import org.quipu.dto.AccountDto;
import org.quipu.dto.BookDto;
import org.quipu.dto.CurrencyDto;
import org.quipu.dto.TransactionDto;
import org.quipu.domain.Account;
import org.quipu.domain.Book;
import org.quipu.domain.BookUser;
import org.quipu.domain.Category;
import org.quipu.domain.Currency;
import org.quipu.domain.CurrencyDic;
import org.quipu.domain.Debt;
import org.quipu.domain.Settings;
import org.quipu.domain.Tag;
import org.quipu.domain.Transaction;
import org.quipu.domain.TransactionTagMap;
import org.quipu.domain.User;
import org.quipu.dto.AccountDetailDto;
import org.quipu.dto.BookCacheDto;
import org.quipu.dto.BookUserDto;
import org.quipu.dto.CategoryDto;
import org.quipu.dto.DebtDto;
import org.quipu.dto.TagDto;
import org.quipu.dto.TransactionsFilterDto;
import org.quipu.dto.TransactionsFilterDto.AccountsInfo;
import org.quipu.utils.DaoHelper;
import org.quipu.utils.DateUtils;

@Stateless
@ConcurrencyManagement(value = ConcurrencyManagementType.BEAN)
@TransactionManagement(value = TransactionManagementType.CONTAINER)
@TransactionAttribute(value = TransactionAttributeType.SUPPORTS)
public class BookHelperImpl implements BookHelper {

    @PersistenceContext(unitName = "quipuPU")
    private EntityManager em;

    private Logger logger = Logger.getLogger(getClass());

    public enum SortOrder {
        ASCENDING,
        DESCENDING
    }

    public enum AccountsSortBy {
        TITLE,
        CREATION_DATE,
        INITIAL_BALLANCE,
        BALLANCE
    }

    public enum TransactionsSortBy {
        DATE,
        SRC_ACCOUNT,
        DST_ACCOUNT,
        SUM,
        CATEGORY
    }

    public enum ExternalDataSystem {
        HOME_BUH_5
    }

    public static class Pair<A, B> {
        private A first;
        private B second;

        public Pair(A first, B second) {
            this.first = first;
            this.second = second;
        }

        public A getFirst() {
            return first;
        }

        public void setFirst(A first) {
            this.first = first;
        }

        public B getSecond() {
            return second;
        }

        public void setSecond(B second) {
            this.second = second;
        }
    }

    public class AccountSrcDst extends Pair<Account, Account> {
        public AccountSrcDst(Account srcAccount, Account dstAccount) {
            super(srcAccount, dstAccount);
        }
    }


    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public Map<String, String> getSettings(String login) throws AppException {
        logger.debug("Fetching settings for user " + login);
        Map<String, String> userSettings = new HashMap<String, String>();
        try {
            Query q;
            if(login == null) {
                q = em.createQuery("select s from Settings s where s.user is null");
            } else {
                User user = findUser(login);
                q = em.createQuery("select s from Settings s where s.user.id = :id");
                q.setParameter("id", user.getId());
            }
            List<Settings> settings = q.getResultList();
            for(Settings s: settings) {
                userSettings.put(s.getKey(), s.getValue());
            }
        } catch (Exception ex) {
            throw new AppException("Failed to fetch settings for user " + login, ex);
        }
        return userSettings;
    }

    @TransactionAttribute(TransactionAttributeType.SUPPORTS)
    private User findUser(Long userId) throws NoSuchUserException {
        User user = em.find(User.class, userId);
        if(user == null)
            throw new NoSuchUserException("Could't find user with id " + userId);
        return user;
    }

    @TransactionAttribute(TransactionAttributeType.SUPPORTS)
    private User findUser(String login) throws AppException {
        try {
            Query q = em.createQuery("select u from User u where u.login = :login");
            q.setParameter("login", login);
            return (User) q.getSingleResult();
        } catch(NoResultException ex) {
            throw new NoSuchUserException("Could't find user with login " + login);
        } catch(Exception ex) {
            throw new AppException("Failed to fetch user with login " + login, ex);
        }
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    private List<BookDto> getUserBooks(User user) throws AppException {
        List<BookDto> books = new LinkedList<BookDto>();
        try {
            Query q;
            if(user == null) {
                q = em.createQuery("select bu from BookUser bu where bu.user is null");
            } else {
                q = em.createQuery("select bu from BookUser bu where bu.user.id = :userId");
                q.setParameter("userId", user.getId());
            }
            List<BookUser> userBooks = q.getResultList();
            for(BookUser bookUser: userBooks) {
                BookDto bookDto = createBookDto(bookUser);
                books.add(bookDto);
            }
        } catch (Exception ex) {
            throw new AppException("Failed to fetch books user " + user + " has access to", ex);
        }
        return books;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List<BookDto> getUserBooks(Long userId) throws AppException {
        User user = null;
        try {
            if(userId != null)
                user = findUser(userId);
        } catch (AppException ex) {
            throw new AppException("Failed to fetch books user " + userId + " has access to", ex);
        }
        return getUserBooks(user);
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List<BookDto> getUserBooks(String userLogin) throws AppException {
        User user = null;
        try {
            if(userLogin != null)
                user = findUser(userLogin);
        } catch (AppException ex) {
            throw new AppException("Failed to fetch books user " + userLogin + " has access to", ex);
        }
        return getUserBooks(user);
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public Double getInitialBalance(Long accountId, Long currencyId, Date date) {
        Double balance0 = 0.0;
        TransactionsFilter filter = new TransactionsFilter();
        And and = new And();
        filter.setCondition(and);
        and.addCondition(new DstIs(accountId));
        and.addCondition(new CurrencyIs(currencyId));
        and.addCondition(new IsInitialBallance());
        if(date != null)
            and.addCondition(new DateEqualsBefore(date));
        String querySql = "SELECT sum(t.sum) FROM Transaction t WHERE " + filter.getQueryConditions();
        Query query = em.createQuery(querySql);
        filter.setParameter(query);
        balance0 = (Double) query.getSingleResult();
        if(balance0 == null)
            balance0 = 0.0;
        return balance0;
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public BookUser getBookUser(Long bookId, Long userId) throws AppException {
        try {
            TypedQuery<BookUser> q = em.createQuery("SELECT bu FROM BookUser bu WHERE bu.book.id = :bookId AND bu.user.id = :userId", BookUser.class);
            q.setParameter("bookId", bookId);
            q.setParameter("userId", userId);
            return q.getSingleResult();
        } catch(NoResultException ex) {
            logger.warn("BookUser not found for bookId " + bookId + " and userId " + userId, ex);
        }
        return null;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public BookUserDto getBookUserDto(Long bookId, Long userId) throws AppException {
        BookUserDto bookUserDto = null;
        BookUser bookUser = getBookUser(bookId, userId);
        if(bookUser != null) {
            bookUserDto = new BookUserDto();
            bookUserDto.setBookId(bookId);
            bookUserDto.setUserId(userId);
            bookUserDto.setOwner(bookUser.getOwner());
            bookUserDto.setReadonly(bookUser.getReadonly());
        }
        return bookUserDto;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List<CurrencyDto> getBookCurrencies(Long bookId) throws AppException {
        List<CurrencyDto> currencyDtos = new LinkedList<CurrencyDto>();
        TypedQuery<Currency> q = em.createQuery("SELECT c FROM Currency c WHERE c.book.id = :bookId", Currency.class);
        q.setParameter("bookId", bookId);
        List<Currency> currencies = q.getResultList();
        for(Currency currency: currencies) {
            CurrencyDto currencyDto = new CurrencyDto();
            currencyDto.setId(currency.getId());
            currencyDto.setBookId(currency.getBook().getId());
            if(currency.getDicCurrency() != null) {
                currencyDto.setDicCurrencyId(currency.getDicCurrency().getId());
            }
            currencyDto.setTitle(currency.getTitle());
            currencyDtos.add(currencyDto);
        }
        return currencyDtos;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List<TagDto> getBookTags(Long bookId) throws AppException {
        List<TagDto> tagDtos = new LinkedList<TagDto>();
        String strQuery = "SELECT t, COUNT(tmap.transaction.id)"
                            + " FROM"
                            + "	Tag t"
                            + " LEFT JOIN"
                            + "	t.transactionMapping tmap"
                            + " WHERE t.book.id = :bookId"
                            + " GROUP BY t";
        Query q = em.createQuery(strQuery);
        q.setParameter("bookId", bookId);
        List<Object[]> result = q.getResultList();
        for(Object[] row: result) {
            Tag tag = (Tag) row[0];
            TagDto tagDto = new TagDto();
            tagDto.setId(tag.getId());
            tagDto.setBookId(tag.getBook().getId());
            tagDto.setTitle(tag.getTitle());
            tagDto.setFrequency((Long) (row[1]));
            tagDtos.add(tagDto);
        }
        return tagDtos;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List<CategoryDto> getBookCategoriesTree(Long bookId, CategoryDto.Type type) throws AppException {
        List<CategoryDto> categoryDtos = new LinkedList<CategoryDto>();
        TypedQuery<Category> q = em.createQuery("SELECT c FROM Category c WHERE c.parentCategory is null AND c.type = :type AND c.book.id = :bookId", Category.class);
        Transaction.Type trType = (CategoryDto.Type.INCOME.equals(type)? Transaction.Type.INCOME: Transaction.Type.OUTCOME);
        q.setParameter("type", trType);
        q.setParameter("bookId", bookId);
        List<Category> rootCategories = q.getResultList();
        for(Category rootCategory: rootCategories) {
            CategoryDto categoryDto = buildCategory(rootCategory, null, type);
            categoryDtos.add(categoryDto);
        }
        return categoryDtos;
    }

    private CategoryDto buildCategory(Category category, Long parentId, CategoryDto.Type type) {
        CategoryDto parentDto = new CategoryDto();
        parentDto.setId(category.getId());
        parentDto.setBookId(category.getBook().getId());
        parentDto.setParentId(parentId);
        parentDto.setTitle(category.getTitle());
        parentDto.setType(type);
        parentDto.setDepth(category.getDepth());
        for(Category child: category.getChilds()) {
            CategoryDto childDto = buildCategory(child, category.getId(), type);
            parentDto.addChild(childDto);
        }
        return parentDto;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List<AccountDto> getBookAccounts(Long bookId) throws AppException {
        List<AccountDto> accountDtos = new LinkedList<AccountDto>();
        TypedQuery<Account> q = em.createQuery("SELECT a FROM Account a WHERE a.book.id = :bookId", Account.class);
        q.setParameter("bookId", bookId);
        List<Account> accounts = q.getResultList();
        for(Account account: accounts) {
            AccountDto accountDto = new AccountDto();
            accountDto.setId(account.getId());
            accountDto.setTitle(account.getTitle());
            accountDto.setCreationDate(account.getCreationDate());
            accountDto.setBookId(account.getBook().getId());
            if(account.getDebt() != null) {
                DebtDto debtDto = new DebtDto();
                debtDto.setId(account.getDebt().getId());
                debtDto.setPercent(account.getDebt().getPercent());
                accountDto.setDebt(debtDto);
            }
            accountDtos.add(accountDto);
        }
        return accountDtos;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List<AccountDetailDto> getBookAccountsCalculation(Long bookId, Date date, boolean approvedOnly) throws AppException {
        List<AccountDetailDto> accountCalcList = new LinkedList<AccountDetailDto>();
        List<AccountDto> accounts = getBookAccounts(bookId);
        AccountDetailDto accountCalc;
        Double balance;
        Double initBalance;
        for(AccountDto account: accounts) {
            accountCalc = new AccountDetailDto();
            accountCalc.setAccount(account);
            account.clearInitialBallance();
            List<Currency> currencies = getAccountUsedCurrencies(account.getId(), date, approvedOnly);
            for(Currency currency: currencies) {
                balance = getBallance(account.getId(), currency.getId(), date, approvedOnly);
                if(balance == null) {
                    balance = 0.0;
                }
                accountCalc.addCurrencyBalance(currency.getId(), balance);
                if(!account.isDebt()) {
                    initBalance = getInitialBalance(account.getId(), currency.getId(), date);
                    account.addInitialBallance(currency.getId(), initBalance);
                }
            }
            accountCalcList.add(accountCalc);
        }
        return accountCalcList;
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    private List<Currency> getAccountUsedCurrencies(Long accountId, Date date, boolean approvedOnly) {
        List<Currency> currencies = new LinkedList<Currency>();
        TransactionsFilter filter = new TransactionsFilter();
        And and = new And();
        filter.setCondition(and);
        if(approvedOnly) {
            and.addCondition(new IsApproved());
        }
        if(date != null) {
            and.addCondition(new DateEqualsBefore(date));
        }
        Or or = new Or();
        or.addCondition(new SrcIs(accountId));
        and.addCondition(or);
        String querySql = "SELECT DISTINCT t.currency FROM Transaction t WHERE " + filter.getQueryConditions();
        TypedQuery<Currency> query = em.createQuery(querySql, Currency.class);
        filter.setParameter(query);
        currencies.addAll(query.getResultList());

        or.clear();
        or.addCondition(new DstIs(accountId));
        Condition convertCondition = new TransactionsFilter.IsNotCurrencyConvertion();
        and.addCondition(convertCondition);
        querySql = "SELECT DISTINCT t.currency FROM Transaction t WHERE " + filter.getQueryConditions();
        query = em.createQuery(querySql, Currency.class);
        filter.setParameter(query);
        currencies.addAll(query.getResultList());

        and.removeCondition(convertCondition);
        convertCondition = new TransactionsFilter.IsCurrencyConvertion();
        and.addCondition(convertCondition);
        querySql = "SELECT DISTINCT t.paymentCurrency FROM Transaction t WHERE " + filter.getQueryConditions();
        query = em.createQuery(querySql, Currency.class);
        filter.setParameter(query);
        currencies.addAll(query.getResultList());
        return currencies;
    }

    public Double getBallance(Long accountId, Long currencyId, Date date, boolean approvedOnly) {
        TransactionsFilter filter = new TransactionsFilter();
        And and = new And();
        filter.setCondition(and);
        Condition curCondition = new PaymentCurrencyIs(currencyId);
        and.addCondition(curCondition);
        if(approvedOnly)
            and.addCondition(new IsApproved());
        if(date != null)
            and.addCondition(new DateEqualsBefore(date));
        Condition accountCond = new DstIs(accountId);
        and.addCondition(accountCond);
        Condition convertCondition = new TransactionsFilter.IsCurrencyConvertion();
        and.addCondition(convertCondition);

        String querySql = "SELECT sum(t.paymentSum) FROM Transaction t WHERE " + filter.getQueryConditions();
        Query query = em.createQuery(querySql);
        filter.setParameter(query);
        Double incomeSum1 = (Double) query.getSingleResult();
        if(incomeSum1 == null) {
            incomeSum1 = 0.0;
        }

        and.removeCondition(curCondition);
        and.removeCondition(convertCondition);
        curCondition = new CurrencyIs(currencyId);
        and.addCondition(curCondition);
        convertCondition = new TransactionsFilter.IsNotCurrencyConvertion();
        and.addCondition(convertCondition);
        querySql = "SELECT sum(t.sum) FROM Transaction t WHERE " + filter.getQueryConditions();
        query = em.createQuery(querySql);
        filter.setParameter(query);
        Double incomeSum2 = (Double) query.getSingleResult();
        if(incomeSum2 == null) {
            incomeSum2 = 0.0;
        }

        and.removeCondition(convertCondition);
        and.removeCondition(accountCond);
        accountCond = new SrcIs(accountId);
        and.addCondition(accountCond);
        querySql = "SELECT sum(t.sum) FROM Transaction t WHERE " + filter.getQueryConditions();
        query = em.createQuery(querySql);
        filter.setParameter(query);
        Double outcomeSum = (Double) query.getSingleResult();
        if(outcomeSum == null) {
            outcomeSum = 0.0;
        }
        return incomeSum1 + incomeSum2 - outcomeSum;
    }

    @Override
    public Double getInitialBallanceSum(Collection<Long> accountIds, Long currencyId, Date date) {
        Double ballance0 = 0.0;
        TransactionsFilter filter = new TransactionsFilter();
        And and = new And();
        filter.setCondition(and);
        and.addCondition(new AccountsAre(accountIds));
        and.addCondition(new CurrencyIs(currencyId));
        and.addCondition(new IsInitialBallance());
        if(date != null)
            and.addCondition(new DateEqualsBefore(date));
        String querySql = "SELECT sum(t.sum) FROM Transaction t WHERE " + filter.getQueryConditions();
        Query query = em.createQuery(querySql);
        filter.setParameter(query);
        ballance0 = (Double) query.getSingleResult();
        if(ballance0 == null)
            ballance0 = 0.0;
        return ballance0;
    }
/*
    private Double getBallance(Account account, Currency currency, Date date) {
        Double ballance0 = Double.valueOf(0);

        Collection<Transaction> incomes = account.getInTransactions();
        for(Transaction income: incomes) {
            if(income.getCurrency().equals(currency) && income.getType().equals(Transaction.Type.INITIAL_BALLANCE))
                ballance0 = income.getSum();

        }
        return ballance0;
    }
*/
/*
    public Double getBallanceSum(Collection<Account> accounts, Long currencyId, Date date, boolean countUpPlanned) {
        Double ballanceSum = Double.valueOf(0);

        for(Account account: accounts)
            ballanceSum += getBallance(account.getId(), currencyId, date, countUpPlanned);
        return ballanceSum;
    }
*/
    public Double getBallanceSum(Book book, Long currencyId, Date dateTo, boolean approvedOnly) {
        TransactionsFilter filter = new TransactionsFilter();
        And and = new And();
        and.addCondition(new AccountsAre(book.getOwnAccountIds()));
        and.addCondition(new CurrencyIs(currencyId));
        if(approvedOnly)
            and.addCondition(new IsApproved());
        if(dateTo != null)
            and.addCondition(new DateEqualsBefore(dateTo));
        Or typeCondition = new Or();
        and.addCondition(typeCondition);
        filter.setCondition(and);

        typeCondition.addCondition(new IsInitialBallance());
        typeCondition.addCondition(new IsIncome());
        typeCondition.addCondition(new IsIncomeLoan(book));
        String querySql = "SELECT sum(t.sum) FROM Transaction t WHERE " + filter.getQueryConditions();
        Query query = em.createQuery(querySql);
        filter.setParameter(query);
        Double incomeSum = (Double) query.getSingleResult();
        if(incomeSum == null)
            incomeSum = 0.0;

        typeCondition.clear();
        typeCondition.addCondition(new IsOutcome());
        typeCondition.addCondition(new IsOutcomeLoan(book));
        querySql = "SELECT sum(t.sum) FROM Transaction t WHERE " + filter.getQueryConditions();
        query = em.createQuery(querySql);
        filter.setParameter(query);
        Double outcomeSum = (Double) query.getSingleResult();
        if(outcomeSum == null)
            outcomeSum = 0.0;
        return incomeSum - outcomeSum;
    }

    public Double getInitialBallanceSum(Book book, Long currencyId, Date date) {
        return getInitialBallanceSum(DaoHelper.getIds(book.getOwnAccounts()), currencyId, date);
    }
/*
    public Double getBallanceSum(Book book, Long currencyId, Date date, boolean countUpPlanned) {
        return getBallanceSum(book.getOwnAccounts(), currencyId, date, countUpPlanned);
    }
*/
    public Double getLoan(Book book, Long accountId, Long currencyId, Date date, boolean approvedOnly) {
        TransactionsFilter filter = new TransactionsFilter();
        And and = new And();
        filter.setCondition(and);
        and.addCondition(new CurrencyIs(currencyId));
        and.addCondition(new IsIncomeLoan(book));
        and.addCondition(new SrcIs(accountId));
        if(date != null)
            and.addCondition(new DateEqualsBefore(date));
        if(approvedOnly)
            and.addCondition(new IsApproved());
        String querySql = "SELECT sum(t.sum) FROM Transaction t WHERE " + filter.getQueryConditions();
        Query query = em.createQuery(querySql);
        filter.setParameter(query);
        Double loanSum = (Double) query.getSingleResult();
        if(loanSum == null)
            loanSum = 0.0;
        return loanSum;
    }

    public Double getLoanSum(Book book, Long currencyId, Date date, boolean approvedOnly) {
        TransactionsFilter filter = new TransactionsFilter();
        And and = new And();
        filter.setCondition(and);
        and.addCondition(new CurrencyIs(currencyId));
        and.addCondition(new IsIncomeLoan(book));
        if(date != null)
            and.addCondition(new DateEqualsBefore(date));
        if(approvedOnly)
            and.addCondition(new IsApproved());
        String querySql = "SELECT sum(t.sum) FROM Transaction t WHERE " + filter.getQueryConditions();
        Query query = em.createQuery(querySql);
        filter.setParameter(query);
        Double loanSum = (Double) query.getSingleResult();
        if(loanSum == null)
            loanSum = 0.0;
        return loanSum;
    }

    public Double getRepayment(Book book, Long accountId, Long currencyId, Date date, boolean approvedOnly) {
        TransactionsFilter filter = new TransactionsFilter();
        And and = new And();
        filter.setCondition(and);
        and.addCondition(new CurrencyIs(currencyId));
        and.addCondition(new IsOutcomeLoan(book));
        and.addCondition(new DstIs(accountId));
        if(date != null)
            and.addCondition(new DateEqualsBefore(date));
        if(approvedOnly)
            and.addCondition(new IsApproved());
        String querySql = "SELECT sum(t.sum) FROM Transaction t WHERE " + filter.getQueryConditions();
        Query query = em.createQuery(querySql);
        filter.setParameter(query);
        Double repaymentSum = (Double) query.getSingleResult();
        if(repaymentSum == null)
            repaymentSum = 0.0;
        return repaymentSum;
    }

    public Double getRepaymentSum(Book book, Long currencyId, Date date, boolean approvedOnly) {
        TransactionsFilter filter = new TransactionsFilter();
        And and = new And();
        filter.setCondition(and);
        and.addCondition(new CurrencyIs(currencyId));
        and.addCondition(new IsOutcomeLoan(book));
        if(date != null)
            and.addCondition(new DateEqualsBefore(date));
        if(approvedOnly)
            and.addCondition(new IsApproved());
        String querySql = "SELECT sum(t.sum) FROM Transaction t WHERE " + filter.getQueryConditions();
        Query query = em.createQuery(querySql);
        filter.setParameter(query);
        Double repaymentSum = (Double) query.getSingleResult();
        if(repaymentSum == null)
            repaymentSum = 0.0;
        return repaymentSum;
    }

    public Date currentDate() {
        return Calendar.getInstance().getTime();
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    private <T> T findObject(Class<T> objClass, Long id) throws AppException {
        T obj = (T) em.find(objClass, id);
        if (obj == null) {
            throw new NoSuchObjectException("Could't find object of type " + objClass.getSimpleName() + " with id " + id);
        }
        return obj;
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    private Book findBook(Long bookId) throws AppException {
        Book book = em.find(Book.class, bookId);
        if(book == null)
            throw new NoSuchBookException("Could't find book with id " + bookId);
        return book;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void submitBook(BookDto bookDto, Long userId) throws AppException {
        Book book;
        if(bookDto.getId() == null) {
            User user = findUser(userId);
            book = new Book();
            book.setTitle(bookDto.getTitle());
            BookUser bookUser = new BookUser();
            bookUser.setBook(book);
            bookUser.setUser(user);
            bookUser.setOwner(true);
            bookUser.setReadonly(false);
            em.merge(bookUser);
        } else {
            book = findBook(bookDto.getId());
            book.setTitle(bookDto.getTitle());  //todo: reimplement - avoid duplicate code
        }
        em.merge(book);
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void removeBook(Long bookId) throws AppException {
        Book book = findBook(bookId);
        em.remove(book);
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public BookCacheDto getBookCache(Long bookId, Long userId) throws AppException {
        BookUser bookUser = getBookUser(bookId, userId);
        if(bookUser != null) {
            return createBookCache(bookUser);
        }
        return null;
    }

    @TransactionAttribute(TransactionAttributeType.SUPPORTS)
    private CurrencyDic findDicCurrency(Long dicCurrencyId) throws AppException {
        CurrencyDic dicCurrency = em.find(CurrencyDic.class, dicCurrencyId);
        if(dicCurrency == null)
            throw new NoSuchCurrencyException("Could't find global currency with id " + dicCurrencyId);
        return dicCurrency;
    }

    @TransactionAttribute(TransactionAttributeType.SUPPORTS)
    private Currency findCurrency(Long currencyId) throws AppException {
        Currency currency = em.find(Currency.class, currencyId);
        if(currency == null)
            throw new NoSuchCurrencyException("Could't find currency with id " + currencyId);
        return currency;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void createOrUpdateCurrency(CurrencyDto currencyDto) throws AppException {
        Currency currency;
        if(currencyDto.getId() == null) {
            Book book = findBook(currencyDto.getBookId());
            currency = new Currency();
            currency.setBook(book);
        } else {
            currency = findCurrency(currencyDto.getId());
        }
        currency.setTitle(currencyDto.getTitle());
        if (currencyDto.getDicCurrencyId() != null) {
            currency.setDicCurrency(findDicCurrency(currencyDto.getDicCurrencyId()));
        } else {
            currency.setDicCurrency(null);
        }
        em.merge(currency);
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void removeCurrency(Long currencyId) throws AppException {
        Currency currency = findCurrency(currencyId);
        em.remove(currency);
    }

    @TransactionAttribute(TransactionAttributeType.SUPPORTS)
    private Tag findTag(Long tagId) throws AppException {
        Tag tag = em.find(Tag.class, tagId);
        if(tag == null)
            throw new NoSuchTagException("Could't find tag with id " + tagId);
        return tag;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void submitTag(TagDto tagDto) throws AppException {
        Tag tag;
        if(tagDto.isNew()) {
            Book book = findBook(tagDto.getBookId());
            tag = new Tag();
            tag.setBook(book);
        } else {
            tag = findTag(tagDto.getId());
        }
        tag.setTitle(tagDto.getTitle());
        em.merge(tag);
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void removeTag(Long tagId) throws AppException {
        Tag tag = findTag(tagId);
        em.remove(tag);
    }

    @TransactionAttribute(TransactionAttributeType.SUPPORTS)
    private Category findCategory(Long categoryId) throws AppException {
        Category category = em.find(Category.class, categoryId);
        if(category == null)
            throw new NoSuchTagException("Could't find category with id " + categoryId);
        return category;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void createOrUpdateCategory(CategoryDto categoryDto) throws AppException {
        if(categoryDto == null) {
            throw new IllegalArgumentException("Category to create/update is null");
        }
        Category category;
        if(categoryDto.getId() == null) {
            Book book = findBook(categoryDto.getBookId());
            category = new Category();
            category.setBook(book);
            if (CategoryDto.Type.INCOME.equals(categoryDto.getType())) {
                category.setType(Transaction.Type.INCOME);
            } else if (CategoryDto.Type.OUTCOME.equals(categoryDto.getType())) {
                category.setType(Transaction.Type.OUTCOME);
            }
        } else {
            category = findCategory(categoryDto.getId());
        }
        category.setTitle(categoryDto.getTitle());
        Category parentCategory = null;
        if(categoryDto.getParentId() != null) {
            parentCategory = findCategory(categoryDto.getParentId());
        }
        category.setParentCategory(parentCategory);
        em.merge(category);
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void removeCategory(Long categoryId) throws AppException {
        Category category = findCategory(categoryId);
        em.remove(category);
    }

    @TransactionAttribute(TransactionAttributeType.SUPPORTS)
    private Account findAccount(Long accountId) throws AppException {
        Account account = em.find(Account.class, accountId);
        if(account == null)
            throw new NoSuchAccountException("Could't find account with id " + accountId);
        return account;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void createOrUpdateAccount(AccountDto accountDto) throws AppException {
        Book book = findBook(accountDto.getBookId());
        Account account;
        boolean newAccount = (accountDto.getId() == null);
        if(newAccount){
            account = new Account();
        } else {
            account = findAccount(accountDto.getId());
        }
        account.setTitle(accountDto.getTitle());
        account.setCreationDate(accountDto.getCreationDate());
        account.setBook(book);
        Debt debt = account.getDebt();
        DebtDto debtDto = accountDto.getDebt();
        if(debtDto != null) {
            if(debt == null) {
                debt = new Debt();
            }
            debt.setPercent(debtDto.getPercent());
            debt.setEndDate(debtDto.getEndDate());
            debt.setPayDay(debtDto.getPayDay());
            debt.setMonthlySum(debtDto.getMonthlySum());
            debt.setFormula(debtDto.getFormula());
            account.setDebt(debt);
        } else {
            account.setDebt(null);
            if(debt != null) {
                em.remove(debt);
            }
        }
        account = em.merge(account);

        if(!newAccount) {
            TransactionsFilter filter = new TransactionsFilter();
            And and = new And();
            filter.setCondition(and);
            and.addCondition(new DstIs(account.getId()));
            and.addCondition(new IsInitialBallance());
            List<Transaction> initTransactions = findTransactions(filter);
            for(Transaction tr: initTransactions) {
                em.remove(tr);
            }
        }
        Map<Long, Double> initialSumList = accountDto.getInitialBallances();
        if(initialSumList != null) {
            Currency currency;
            for(Map.Entry<Long, Double> initialSum: initialSumList.entrySet()) {
                currency = findCurrency(initialSum.getKey());
                Transaction transaction = new Transaction();
                transaction.setDate(accountDto.getCreationDate());
                transaction.setDstAccount(account);
                transaction.setCurrency(currency);
                transaction.setSum(initialSum.getValue());
                transaction.setDescription("initial sum");
                transaction.setApproved(true);
                em.persist(transaction);
            }
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void removeAccount(Long accountId) throws AppException {
        Account account = findAccount(accountId);
        em.remove(account);
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    private List<Currency> getTransactionsCurrencies(TransactionsFilter filter) {
        List<Currency> currencies = new LinkedList<Currency>();
        String querySql = "SELECT DISTINCT t.currency FROM Transaction t WHERE " + filter.getQueryConditions();
        TypedQuery<Currency> query = em.createQuery(querySql, Currency.class);
        filter.setParameter(query);
        currencies.addAll(query.getResultList());

        querySql = "SELECT DISTINCT t.paymentCurrency FROM Transaction t WHERE " + filter.getQueryConditions();
        query = em.createQuery(querySql, Currency.class);
        filter.setParameter(query);
        currencies.addAll(query.getResultList());
        return currencies;
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    private Transaction findTransaction(Long transactionId) throws AppException {
        Transaction transaction = em.find(Transaction.class, transactionId);
        if(transaction == null)
            throw new NoSuchTransactionException("Could't find transaction with id " + transactionId);
        return transaction;
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    private List<Transaction> findTransactions(TransactionsFilter filter) throws AppException {
        return findTransactions(filter, 0, 0);
    }

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    private List<Transaction> findTransactions(TransactionsFilter filter, int page, int pageSize) throws AppException {
        String querySql = "SELECT t FROM Transaction t WHERE " + filter.getQueryConditions();
        if(Order.DESC.equals(filter.getOrder())) {
            querySql += " ORDER BY t.date DESC";
        } else {
            querySql += " ORDER BY t.date ASC";
        }
        TypedQuery<Transaction> query = em.createQuery(querySql, Transaction.class);
        filter.setParameter(query);
        if(page >= 0 && pageSize > 0) {
            query.setFirstResult(page * pageSize);
            query.setMaxResults(pageSize);
        }
        return query.getResultList();
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List<TransactionDto> findTransactions(Long bookId, TransactionsFilterDto filterDto, int page, int pageSize) throws AppException {
        List<TransactionDto> transactionDtos = new LinkedList<TransactionDto>();
        And and = new And();
        and.addCondition(new IsNotInitialBallance());
        if(!filterDto.isIncomes() || !filterDto.isOutcomes() || !filterDto.isTransfers()) {
            Or or = new Or();
            if(filterDto.isIncomes()) {
                or.addCondition(new IsIncome());
            }
            if(filterDto.isOutcomes()) {
                or.addCondition(new IsOutcome());
            }
            if(filterDto.isTransfers()) {
                Book book = findBook(bookId);
                or.addCondition(new TransactionsFilter.IsLocalTransfer(book));
                or.addCondition(new TransactionsFilter.IsIncomeLoan(book));
                or.addCondition(new TransactionsFilter.IsOutcomeLoan(book));
            }
            if(or.isEmpty()) {
                return transactionDtos;
            }
            and.addCondition(or);
        }
        if(filterDto.isApprovedOnly()) {
            and.addCondition(new IsApproved());
        }
        if(filterDto.getDateFrom() != null) {
            and.addCondition(new TransactionsFilter.DateEqualsAfter(filterDto.getDateFrom()));
        }
        if(filterDto.getDateTo()!= null) {
            and.addCondition(new TransactionsFilter.DateEqualsBefore(filterDto.getDateTo()));
        }
        if(!filterDto.getAccountsInfo().isEmpty()) {
            Or or = new Or();
            And pairAnd;
            for(AccountsInfo accountInfo: filterDto.getAccountsInfo()) {
                pairAnd = new And();
                if(accountInfo.getSrcAccountId() != null) {
                    pairAnd.addCondition(new SrcIs(accountInfo.getSrcAccountId()));
                }
                if(accountInfo.getDstAccountId() != null) {
                    pairAnd.addCondition(new DstIs(accountInfo.getDstAccountId()));
                }
                or.addCondition(pairAnd);
            }
            and.addCondition(or);
        } else {
            List<Long> accountIds = new LinkedList<Long>();
            List<AccountDto> bookAccounts = getBookAccounts(bookId);
            for(AccountDto account: bookAccounts) {
                accountIds.add(account.getId());
            }
            and.addCondition(new TransactionsFilter.AccountsAre(accountIds));
        }
        Or or = new Or();
        for(CategoryDto.Type type: CategoryDto.Type.values()) {
            if(filterDto.getCategoryIds(type) != null) {
                for(Long id: filterDto.getCategoryIds(type)) {
                    Category category = em.find(Category.class, id);
                    or.addCondition(new TransactionsFilter.IsInCategory(category));
                }
            }
        }
        if(!or.isEmpty()) {
            and.addCondition(or);
        }
        if(filterDto.getTagIds() != null && !filterDto.getTagIds().isEmpty()) {
            and.addCondition(new TagsAre(filterDto.getTagIds()));
        }
        TransactionsFilter filter = new TransactionsFilter();
        filter.setCondition(and);
        if(TransactionsFilterDto.Order.DESC.equals(filterDto.getOrder())) {
            filter.setOrder(Order.DESC);
        } else {
            filter.setOrder(Order.ASC);
        }

        String querySql = "SELECT t FROM Transaction t WHERE " + filter.getQueryConditions();
        if(Order.DESC.equals(filter.getOrder())) {
            querySql += " ORDER BY t.date DESC, t.id DESC";
        } else {
            querySql += " ORDER BY t.date ASC, t.id ASC";
        }
        TypedQuery<Transaction> query = em.createQuery(querySql, Transaction.class);
        filter.setParameter(query);
        if(page >= 0 && pageSize > 0) {
            query.setFirstResult(page * pageSize);
            query.setMaxResults(pageSize);
        }
        List<Transaction> transactions = query.getResultList();
        for(Transaction tr: transactions) {
            if(tr.isInitialIncome()) {
                continue;
            }
            TransactionDto trDto = new TransactionDto();
            trDto.setId(tr.getId());
            trDto.setApproved(tr.getApproved());
            trDto.setDate(tr.getDate());
            trDto.setDescription(tr.getDescription());
            if(tr.getSrcAccount() != null) {
                trDto.setSrcAccountId(tr.getSrcAccount().getId());
            }
            if(tr.getDstAccount() != null) {
                trDto.setDstAccountId(tr.getDstAccount().getId());
            }
            if(tr.getPaymentCurrency() != null) {
                trDto.setSum(tr.getPaymentSum());
                trDto.setCurrencyId(tr.getPaymentCurrency().getId());
                trDto.setConvertCurrencyRate(tr.getSum() / tr.getPaymentSum());
                trDto.setConvertCurrencyId(tr.getCurrency().getId());
            } else {
                trDto.setSum(tr.getSum());
                trDto.setCurrencyId(tr.getCurrency().getId());
            }
            if(tr.getCategory() != null) {
                trDto.setCategoryId(tr.getCategory().getId());
            }
            Tag tag;
            for(TransactionTagMap tagMap: tr.getTagMapping()) {
                tag = tagMap.getTag();
                trDto.addTag(new TagDto(tag.getBook().getId(), tag.getId(), tag.getTitle(), 0));
            }
            transactionDtos.add(trDto);
        }
        return transactionDtos;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void submitTransaction(TransactionDto transactionDto) throws AppException {
        Transaction transaction;
        if(transactionDto.isNew()) {
            transaction = new Transaction();
        } else {
            transaction = findObject(Transaction.class, transactionDto.getId());
//            transaction = em.find(Transaction.class, transactionDto.getId());
        }
        if(transaction == null) {
            throw new AppException("Transaction with id " + transactionDto.getId() + " not found");
        }
        transaction.setApproved(transactionDto.isApproved());
        transaction.setDate(transactionDto.getDate());
        Currency sumCurrency;
        Double sum;
        Currency paymentCurrency = null;
        Double paymentSum = null;
        if(transactionDto.getSum() == null || transactionDto.getSum() <= 0) {
            throw new AppException("Sum must be positive");
        }
        if(transactionDto.getConvertCurrencyId() == null) {
            sumCurrency = em.find(Currency.class, transactionDto.getCurrencyId());
            sum = transactionDto.getSum();
        } else if(transactionDto.getConvertCurrencyRate() != null) {
            sumCurrency = em.find(Currency.class, transactionDto.getConvertCurrencyId());
            sum = transactionDto.getSum() * transactionDto.getConvertCurrencyRate();
            paymentCurrency = em.find(Currency.class, transactionDto.getCurrencyId());
            paymentSum = transactionDto.getSum();
        } else {
            throw new AppException("Currency rate is not set");
        }
        transaction.setSum(sum);
        transaction.setCurrency(sumCurrency);
        transaction.setPaymentSum(paymentSum);
        transaction.setPaymentCurrency(paymentCurrency);
        transaction.setDescription(transactionDto.getDescription());
        if(transactionDto.getCategoryId() != null) {
            Category category = em.find(Category.class, transactionDto.getCategoryId());
            transaction.setCategory(category);
        }
        if(transactionDto.getSrcAccountId() != null) {
            Account srcAccount = em.find(Account.class, transactionDto.getSrcAccountId());
            transaction.setSrcAccount(srcAccount);
        }
        if(transactionDto.getDstAccountId() != null) {
            Account dstAccount = em.find(Account.class, transactionDto.getDstAccountId());
            transaction.setDstAccount(dstAccount);
        }
        transaction = em.merge(transaction);
        if(transaction.getTagMapping() != null) {
            for(TransactionTagMap tagMapping: transaction.getTagMapping()) {
                em.remove(tagMapping);
            }
        }
        Tag tag;
        for(TagDto tagDto: transactionDto.getTags()) {
            tag = findObject(Tag.class, tagDto.getId());
            TransactionTagMap tagMapping = new TransactionTagMap();
            tagMapping.setTransaction(transaction);
            tagMapping.setTag(tag);
            em.persist(tagMapping);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void detachTransaction(TransactionDto transaction, Long originalId) throws AppException {
        if(!transaction.isNew()) {
            throw new AppException("Detaching transaction must not have id");
        }
        Transaction originalTransaction = findTransaction(originalId);
        Double newSum = originalTransaction.getSum() - transaction.getSum();
        if(newSum <= 0) {
            throw new AppException("Detaching sum must be less then original sum");
        }
        originalTransaction.setSum(newSum);
        em.merge(originalTransaction);
        submitTransaction(transaction);
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public void removeTransaction(Long transactionId) throws AppException {
        Transaction transaction = findTransaction(transactionId);
        em.remove(transaction);
    }

/*
    private void removeTransaction(Transaction transaction) {
        while(!transaction.getTagMapping().isEmpty())
            removeTransactionTagMapping(transaction.getTagMapping().iterator().next());
        em.remove(transaction);
        em.flush();
        if(transaction.getCurrency() != null)
            em.refresh(transaction.getCurrency());
        if(transaction.getCategory() != null)
            em.refresh(transaction.getCategory());
        if(transaction.getSrcAccount() != null)
            em.refresh(transaction.getSrcAccount());
        if(transaction.getDstAccount() != null)
            em.refresh(transaction.getDstAccount());
    }

    public void removeTransactionOnly(Long id) {
        Query query = em.createNamedQuery("Transaction.findById");
        query.setParameter("id", id);
        Transaction transaction = (Transaction) query.getSingleResult();

        EntityTransaction tr = em.getTransaction();
        tr.begin();
        removeTransaction(transaction);
        tr.commit();
    }

    public void approveTransaction(Long id) {
        Query query = em.createNamedQuery("Transaction.findById");
        query.setParameter("id", id);
        Transaction transaction = (Transaction) query.getSingleResult();

        transaction.setApproved(Boolean.TRUE);
        EntityTransaction tr = em.getTransaction();
        tr.begin();
        em.persist(transaction);
        tr.commit();
        em.refresh(transaction);
    }
*/
/*
    public Account createAccount(String title, Date creationDate, Book parentBook, Map<Currency, Double> initialBallances, boolean thirdParty) {
        Account account = null;
        try {
            Query query = em.createNamedQuery("Account.bindByTitleForBook");
            query.setParameter("title", title);
            query.setParameter("bookId", parentBook.getId());
            account = (Account) query.getSingleResult();
        } catch(NoResultException ex) {
            EntityTransaction tr = em.getTransaction();
            tr.begin();
            account = new Account();
            account.setTitle(title);
            account.setCreationDate(creationDate);
            account.setBook(parentBook);
            account.setThirdParty(thirdParty);
            em.persist(account);
            for(Map.Entry<Currency, Double> ballanceEntry: initialBallances.entrySet()) {
                Transaction transaction = new Transaction();
                transaction.setDstAccount(account);
                transaction.setDate(account.getCreationDate());
                transaction.setCurrency(ballanceEntry.getKey());
                transaction.setSum(ballanceEntry.getValue());
                transaction.setApproved(true);
                em.persist(transaction);
                em.refresh(ballanceEntry.getKey());
            }
            tr.commit();
            em.refresh(account);
//            Book mergedParentBook = em.merge(account.getBook());
            em.refresh(account.getBook());
        }
        return account;
    }
*/
/*
    public void updateAccount(Account account, String title, Date creationDate, Map<Currency, Double> initialBallances, boolean thirdParty) {
        account.setTitle(title);
        account.setCreationDate(creationDate);
        account.setThirdParty(thirdParty);
        account = em.merge(account);
//        em.flush();
        EntityTransaction tr = em.getTransaction();
        tr.begin();
        for(Transaction initialIncome: account.getInitialIncomes())
            removeTransaction(initialIncome);
        tr.commit();
        tr = em.getTransaction();
        tr.begin();
        for(Map.Entry<Currency, Double> ballanceEntry: initialBallances.entrySet()) {
            Transaction transaction = new Transaction();
            transaction.setDstAccount(account);
            transaction.setDate(account.getCreationDate());
            transaction.setCurrency(ballanceEntry.getKey());
            transaction.setSum(ballanceEntry.getValue());
            transaction.setApproved(true);
            em.persist(transaction);
            em.refresh(ballanceEntry.getKey());
        }
        tr.commit();
        em.refresh(account);
//            Book mergedParentBook = em.merge(account.getBook());
        em.refresh(account.getBook());
    }
*/
/*
    public Currency createCurrency(String title, Book parentBook) {
        Currency currency = null;
        try {
            Query query = em.createNamedQuery("Currency.findByTitleForBook");
            query.setParameter("title", title);
            query.setParameter("bookId", parentBook.getId());
            currency = (Currency) query.getSingleResult();
        } catch(NoResultException ex) {
            EntityTransaction tr = em.getTransaction();
            currency = new Currency();
            currency.setTitle(title);
            currency.setBook(parentBook);
            tr.begin();
            em.persist(currency);
            tr.commit();
            em.refresh(parentBook);
        }
        return currency;
    }
*/
/*
    private void removeCurrency(Currency currency) {
//        Currency mergedCurrency = em.merge(currency);
        em.refresh(currency);
        while(!currency.getTransactions().isEmpty())
            removeTransaction(currency.getTransactions().iterator().next());
        em.remove(currency);
        em.flush();
        em.refresh(currency.getBook());
    }
*/
/*
    public void removeCurrencyOnly(Long id) {
        Query query = em.createNamedQuery("Currency.findById");
        query.setParameter("id", id);
        Currency currency = (Currency) query.getSingleResult();
//        Book mergedBook = em.merge(account.getBook());
        EntityTransaction tr = em.getTransaction();
        tr.begin();
        removeCurrency(currency);
        tr.commit();
    }
*/
/*
    public Category createCategory(String title, Category parent, Book parentBook, Transaction.Type type) {
        Category category = null;
        try {
            Query query = null;
            if(parent != null) {
                query = em.createNamedQuery("Category.findByTitleAndParentAndTypeForBook");
                query.setParameter("parentId", parent.getId());
            } else {
                query = em.createNamedQuery("Category.findRootByTitleAndTypeForBook");
            }
            query.setParameter("title", title);
            query.setParameter("type", type.getValue());
            query.setParameter("bookId", parentBook.getId());
            category = (Category) query.getSingleResult();
        } catch(NoResultException ex) {
            EntityTransaction tr = em.getTransaction();
            category = new Category();
            category.setTitle(title);
            category.setParentCategory(parent);
            category.setBook(parentBook);
            category.setType(type.getValue());
            tr.begin();
            em.persist(category);
            tr.commit();
            if(parent != null)
                em.refresh(parent);
            em.refresh(parentBook);
        }
        return category;
    }
*/
/*
    private void removeCategory(Category category) {
        em.refresh(category);
        if(category.getChilds().size() > 0) {
            for(Category childCategory: category.getChilds())
                removeCategory(childCategory);
        }

        while(!category.getTransactions().isEmpty())
            removeTransaction(category.getTransactions().iterator().next());
        em.remove(category);
        em.flush();
        if(category.getParentCategory() != null)
            em.refresh(category.getParentCategory());
        em.refresh(category.getBook());
    }
*/
/*
    public void removeCategoryOnly(Long id) {
        Query query = em.createNamedQuery("Category.findById");
        query.setParameter("id", id);
        Category category = (Category) query.getSingleResult();
//        Book mergedBook = em.merge(account.getBook());
        EntityTransaction tr = em.getTransaction();
        tr.begin();
        removeCategory(category);
        tr.commit();    
    }
*/
/*
    public Tag findOrCreateTag(String title, Book parentBook) {
        Tag tag = parentBook.findTag(title);
        if(tag == null) {
            EntityTransaction tr = em.getTransaction();
            tag = new Tag();
            tag.setTitle(title);
            tag.setBook(parentBook);
            tr.begin();
            em.persist(tag);
            tr.commit();
            em.refresh(parentBook);
        }
        return tag;
    }
*/
/*
    private void removeTag(Tag tag) {
//        Currency mergedCurrency = em.merge(currency);
        em.refresh(tag);
        while(!tag.getTransactionMapping().isEmpty())
            removeTransactionTagMapping(tag.getTransactionMapping().iterator().next());
        em.remove(tag);
        em.flush();
        em.refresh(tag.getBook());
    }
*/
/*
    public void removeTagOnly(Long id) {
        Query query = em.createNamedQuery("Currency.findById");
        query.setParameter("id", id);
        Tag tag = (Tag) query.getSingleResult();
        EntityTransaction tr = em.getTransaction();
        tr.begin();
        removeTag(tag);
        tr.commit();
    }
*/
/*
    public TransactionTagMap createTransactionTagMapping(Transaction transaction, Tag tag) {
        TransactionTagMap ttMap = null;
        try {
            Query query = em.createNamedQuery("TransactionTagMap.findByMapping");
            query.setParameter("trId", transaction.getId());
            query.setParameter("tagId", tag.getId());
            ttMap = (TransactionTagMap) query.getSingleResult();
        } catch(NoResultException ex) {
            EntityTransaction tr = em.getTransaction();
            ttMap = new TransactionTagMap();
            ttMap.setTransaction(transaction);
            ttMap.setTag(tag);
            tr.begin();
            em.persist(ttMap);
            tr.commit();
            em.refresh(transaction);
            em.refresh(tag);
        }
        return ttMap;
    }
*/
/*
    public void removeTransactionTagMapping(TransactionTagMap ttMap) {
        em.remove(ttMap);
        em.flush();
        em.refresh(ttMap.getTag());
        em.refresh(ttMap.getTransaction());
    }
*/
/*
    public void removeTransactionTagMappingOnly(Long id) {
        Query query = em.createNamedQuery("TransactionTagMap.findById");
        query.setParameter("id", id);
        TransactionTagMap ttMap = (TransactionTagMap) query.getSingleResult();
        EntityTransaction tr = em.getTransaction();
        tr.begin();
        removeTransactionTagMapping(ttMap);
        tr.commit();
    }
*/
/*
    public Currency findCurrency(Long id) {
        Query query = em.createNamedQuery("Currency.findById");
        query.setParameter("id", id);
        return (Currency) query.getSingleResult();
    }
*/
/*
    public Category findCategory(Long id) {
        Query query = em.createNamedQuery("Category.findById");
        query.setParameter("id", id);
        return (Category) query.getSingleResult();
    }
*/
/*
    public List<Book> getBooks() {
        Query query = em.createNamedQuery("Book.findAll");
        return query.getResultList();
    }
*/
/*
    public List<Currency> getAllCurrencies() {
        Query query = em.createNamedQuery("Currency.findAll");
        return query.getResultList();
    }
*/
/*
    public Pair<Transaction, String> createTransaction(Long srcAccountId, Long dstAccountId, Long currencyId, Long categoryId, Date date, Double sum, String desc, Collection<Tag> tags, boolean approved) {

        Transaction transaction = new Transaction();
        Account srcAccount = null;
        Account dstAccount = null;
        Category category = null;
        Currency currency = null;

        if(srcAccountId != null && srcAccountId > 0) {
            try {
                Query query = em.createNamedQuery("Account.findById");
                query.setParameter("id", srcAccountId);
                srcAccount = (Account) query.getSingleResult();
            } catch(NoResultException ex) {
            }
        }
        transaction.setSrcAccount(srcAccount);
        
        if(dstAccountId != null && dstAccountId > 0) {
            try {
                Query query = em.createNamedQuery("Account.findById");
                query.setParameter("id", dstAccountId);
                dstAccount = (Account) query.getSingleResult();
            } catch(NoResultException ex) {
            }
        }
        transaction.setDstAccount(dstAccount);

        if(srcAccount != null && date.before(srcAccount.getCreationDate()))
            return new Pair<Transaction, String>(null, "Date must not be before source account creationg date");
        if(dstAccount != null && date.before(dstAccount.getCreationDate()))
            return new Pair<Transaction, String>(null, "Date must not be before destination account creationg date");

        if(categoryId != null && categoryId > 0) {
            try {
                Query query = em.createNamedQuery("Category.findById");
                query.setParameter("id", categoryId);
                category = (Category) (Category) query.getSingleResult();
            } catch(NoResultException ex) {
            }
        }
        transaction.setCategory(category);

        if(currencyId != null && currencyId > 0) {
            try {
                Query query = em.createNamedQuery("Currency.findById");
                query.setParameter("id", currencyId);
                currency = (Currency) query.getSingleResult();
            } catch(NoResultException ex) {
            }
        }
        if(currency == null)
            return new Pair<Transaction, String>(null, "Currency should be set");
        transaction.setCurrency(currency);

        transaction.setSum(sum);
        transaction.setDate(date);
        transaction.setDescription(desc);
        transaction.setApproved(approved);
        EntityTransaction tr = em.getTransaction();
        tr.begin();
        em.persist(transaction);
        em.flush();

        if(tags != null) {
            for(Tag tag: tags) {
                TransactionTagMap ttMap = new TransactionTagMap();
                ttMap.setTag(tag);
                ttMap.setTransaction(transaction);
                em.persist(ttMap);
                em.flush();
                em.refresh(tag);
                em.refresh(transaction);
            }
        }

        if(transaction.getSrcAccount() != null)
            em.refresh(transaction.getSrcAccount());
        if(transaction.getDstAccount() != null)
            em.refresh(transaction.getDstAccount());
        em.refresh(transaction.getCurrency());
        if(transaction.getCategory() != null)
            em.refresh(transaction.getCategory());
        tr.commit();
        return new Pair<Transaction, String>(transaction, null);
    }
*/
/*
    public void addTransactionSum(Transaction transaction, Double deltaSum) {
        EntityTransaction tr = em.getTransaction();
        tr.begin();
        transaction.setSum(transaction.getSum() + deltaSum);
        em.merge(transaction);
        tr.commit();
    }
*/
    public static class AccountsComparator implements Comparator<Account> {

        private AccountsSortBy sortBy;
        private SortOrder sortOrder;

        public AccountsComparator(AccountsSortBy sortBy, SortOrder sortOrder) {
            this.sortBy = sortBy;
            this.sortOrder = sortOrder;
        }

        public int compare(Account a1, Account a2) {
            int order = sortOrder.equals(SortOrder.ASCENDING)? 1: -1;
            int result = 0;
            if (sortBy.equals(AccountsSortBy.TITLE)) {
                result = a1.getTitle().compareTo(a2.getTitle());
            } else if (sortBy.equals(AccountsSortBy.CREATION_DATE)) {
                result = a1.getCreationDate().compareTo(a2.getCreationDate());
                if(result == 0)
                    result = a1.getId().compareTo(a2.getId());
            } else {
                result = a1.getTitle().compareTo(a2.getTitle());
            }
            return result * order;
        }
    }

    public static class TransactionsComparator<T extends Transaction> implements Comparator<Transaction> {

        private TransactionsSortBy sortBy;
        private SortOrder sortOrder;

        public TransactionsComparator(TransactionsSortBy sortBy, SortOrder sortOrder) {
            this.sortBy = sortBy;
            this.sortOrder = sortOrder;
        }

        public int compare(Transaction tr1, Transaction tr2) {
            int order = sortOrder.equals(SortOrder.ASCENDING)? 1: -1;
            int result = 0;
            if (sortBy.equals(TransactionsSortBy.SRC_ACCOUNT)) {
                if(tr1.getSrcAccount() == null && tr2.getSrcAccount() == null)
                    result = 0;
                else if(tr1.getSrcAccount() == null)
                    result = -1;
                else if(tr2.getSrcAccount() == null)
                    result = 1;
                else {
                    AccountsComparator accComp = new AccountsComparator(AccountsSortBy.TITLE, SortOrder.ASCENDING);
                    result = accComp.compare(tr1.getSrcAccount(), tr2.getSrcAccount());
                }
            } else if (sortBy.equals(TransactionsSortBy.DST_ACCOUNT)) {
                if(tr1.getDstAccount() == null && tr2.getDstAccount() == null)
                    result = 0;
                else if(tr1.getDstAccount() == null)
                    result = -1;
                else if(tr2.getDstAccount() == null)
                    result = 1;
                else {
                    AccountsComparator accComp = new AccountsComparator(AccountsSortBy.TITLE, SortOrder.ASCENDING);
                    result = accComp.compare(tr1.getDstAccount(), tr2.getDstAccount());
                }
            } else {
                result = tr1.getDate().compareTo(tr2.getDate());
                if(result == 0)
                    result = tr1.getId().compareTo(tr2.getId());
            }
            return result * order;
        }
    }

    public static class CategoryComparator implements Comparator<Category> {

        @Override
        public int compare(Category c1, Category c2) {
            return c1.getTitle().compareTo(c2.getTitle());
        }
    }

    public static List<Currency> getAccountsCurrencies(Collection<Account> accounts, Date date) {
        Set<Currency> currencies = new HashSet<Currency>();
        for(Account account: accounts) {
            for(Transaction income: account.getInTransactions()) {
                if(date == null || !income.getDate().after(date))
                    currencies.add(income.getCurrency());
            }
            for(Transaction outcome: account.getOutTransactions()) {
                if(date == null || !outcome.getDate().after(date))
                    currencies.add(outcome.getCurrency());
            }
        }
        return new LinkedList<Currency>(currencies);
    }

    public Date getFirstTransactionDate(TransactionsFilter filter) {
        String querySql = "SELECT min(t.date) FROM Transaction t WHERE " + filter.getQueryConditions();
        Query query = em.createQuery(querySql);
        filter.setParameter(query);
        Date minDate = (Date) query.getSingleResult();
        return minDate;
    }

    public Date getLastTransactionDate(TransactionsFilter filter) {
        String querySql = "SELECT max(t.date) FROM Transaction t WHERE " + filter.getQueryConditions();
        Query query = em.createQuery(querySql);
        filter.setParameter(query);
        Date maxDate = (Date) query.getSingleResult();
        return maxDate;
    }

    public Collection<Long> getOwnAccountIds(Collection<Account> accounts) {
        Collection<Long> own = new LinkedList<Long>();
        if(accounts != null) {
            for(Account a: accounts) {
                if(!a.isThirdParty())
                    own.add(a.getId());
            }
        }
        return own;
    }

    public Collection<Long> getThirdPartyAccountIds(Collection<Account> accounts) {
        Collection<Long> thitdparty = new LinkedList<Long>();
        if(accounts != null) {
            for(Account a: accounts) {
                if(a.isThirdParty())
                    thitdparty.add(a.getId());
            }
        }
        return thitdparty;
    }

    private BookDto createBookDto(BookUser bookUser) {
        BookDto bookDto = new BookDto();
        bookDto.setId(bookUser.getBook().getId());
        bookDto.setTitle(bookUser.getBook().getTitle());
        bookDto.setOwner(bookUser.getOwner());
        bookDto.setReadonly(bookUser.getReadonly());
        return bookDto;
    }

    private BookCacheDto createBookCache(BookUser bookUser) throws AppException {
        BookCacheDto bookCache = new BookCacheDto();
        bookCache.setBook(createBookDto(bookUser));
        List<CurrencyDto> currencies = getBookCurrencies(bookUser.getBook().getId());
        bookCache.setCurrencies(currencies);
        return bookCache;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List<Map<Long, Double[]>> getBalanceArchive(Long bookId, Date dateFrom, Date dateTo, boolean approvedOnly) throws AppException {
        if(dateFrom != null && dateTo != null && dateFrom.compareTo(dateTo) > 0) {
            throw new AppException("Incorrect date filter");
        }
        Book book = findBook(bookId);
        And root = new And();
        root.addCondition(new AccountsAre(DaoHelper.getIds(book.getOwnAccounts())));
        if(dateFrom != null) {
            root.addCondition(new TransactionsFilter.DateEqualsAfter(dateFrom));
        }
        if(dateTo != null) {
            root.addCondition(new TransactionsFilter.DateEqualsBefore(dateTo));
        }
        if(approvedOnly) {
            root.addCondition(new IsApproved());
        }
        TransactionsFilter filter = new TransactionsFilter(root);
        filter.setOrder(Order.ASC);
        List<Transaction> transactions = findTransactions(filter);
        Map<Long, Double[]> incomes = new HashMap<Long, Double[]>();
        Map<Long, Double[]> outcomes = new HashMap<Long, Double[]>();
        Map<Long, Double[]> balances = new HashMap<Long, Double[]>();
        List<Map<Long, Double[]>> archive = new LinkedList<Map<Long, Double[]>>();
        archive.add(incomes);
        archive.add(outcomes);
        archive.add(balances);
        int day = 0;
        Long curId;
        Long payCurId;
        Transaction tr = null;
        DateTime trDt = null;
        Iterator<Transaction> it = transactions.iterator();
        if(!transactions.isEmpty()) {
            if(dateFrom == null) {
                dateFrom = transactions.get(0).getDate();
            }
            if(dateTo == null) {
                dateTo = transactions.get(transactions.size() - 1).getDate();
            }
            tr = it.next();
            trDt = new DateTime(DateUtils.truncateDate(tr.getDate()));
        }
        int days = Days.daysBetween(new DateTime(dateFrom), new DateTime(dateTo)).getDays() + 1;
        List<Currency> currencies = getAccountsCurrencies(book.getOwnAccounts(), dateTo);
        for(Currency currency: currencies) {
            incomes.put(currency.getId(), new Double[days]);
            outcomes.put(currency.getId(), new Double[days]);
            Double[] curBalances = new Double[days];
            balances.put(currency.getId(), curBalances);
            curBalances[0] = getInitialBallanceSum(book, currency.getId(), dateFrom);
        }
        DateTime endDt = new DateTime(dateTo);
        for(DateTime dt = new DateTime(dateFrom); !dt.isAfter(endDt); dt = dt.plus(Period.days(1)), day++) {
            for(Currency currency: currencies) {
                incomes.get(currency.getId())[day] = 0.0;
                outcomes.get(currency.getId())[day] = 0.0;
                if(day > 0) {
                    balances.get(currency.getId())[day] = balances.get(currency.getId())[day-1];
                }
            }
            while(trDt != null && trDt.isEqual(dt)) {
                curId = tr.getCurrency().getId();
                payCurId = (tr.getPaymentCurrency()!= null? tr.getPaymentCurrency().getId(): null);
                if(tr.isOutcome() || tr.isRepayment()) {
                    outcomes.get(curId)[day] -= tr.getSum();
                    balances.get(curId)[day] -= tr.getSum();
                } else if(tr.isIncome() || tr.isInitialIncome() || tr.isLoan()) {
                    if(payCurId != null) {
                        incomes.get(payCurId)[day] += tr.getPaymentSum();
                        balances.get(payCurId)[day] += tr.getPaymentSum();
                    } else {
                        incomes.get(curId)[day] += tr.getSum();
                        balances.get(curId)[day] += tr.getSum();
                    }
                } else if(tr.isTransfer() && payCurId != null) {
                    incomes.get(payCurId)[day] += tr.getPaymentSum();
                    balances.get(payCurId)[day] += tr.getPaymentSum();
                    outcomes.get(curId)[day] -= tr.getSum();
                    balances.get(curId)[day] -= tr.getSum();
                }
                if(it.hasNext()) {
                    tr = it.next();
                    trDt = new DateTime(DateUtils.truncateDate(tr.getDate()));
                } else {
                    break;
                }
            }
        }
        return archive;
    }
}
